Namespace persistent
    Public Class laLocationDefine
        Public Shared Function msisdnTOcustcode(ByVal msisdn As String) As DataTable
            Dim sql As String
            sql = " select custcode,cslevel,(select custcode from customer_all where customer_id=a.customer_id_high ) from customer_all a where customer_id in "
            sql = sql + " (select customer_id_high from customer_all where customer_id in "
            sql = sql + " (select customer_id_high from customer_all where customer_id= "
            sql = sql + " (select fn_msisdn_to_custid('" & msisdn & "') from dual) "
            sql = sql + " ) and (cslevel='20' or cslevel='30') "
            sql = sql + " ) and (cslevel='10' or cslevel='20') "
            Return myDBConn.myORATable(sql)
        End Function

        Public Shared Function getcustcode(ByVal msisdn As String) As String
            Dim strSQL As String
            strSQL = "Select DECODE(cu2.cslevel, 30, CU3.CUSTCODE, CU2.CUSTCODE) "
            strSQL = strSQL + "FROM contract_all  A, customer_all cu1, customer_all cu2, customer_all cu3 "
            strSQL = strSQL + "WHERE(a.CUSTOMER_ID = CU1.CUSTOMER_ID) "
            strSQL = strSQL + "AND   cu3.customer_id=cu2.customer_id_high "
            strSQL = strSQL + "AND   cu2.customer_id=cu1.customer_id_high "
            strSQL = strSQL + "AND   A.co_id = (select max(B.co_id) "
            strSQL = strSQL + "             from contr_services_cap  B, eu_bill.view_directory_number  C "
            strSQL = strSQL + "               where C.dn_num='" & msisdn & "' "
            strSQL = strSQL + "                and B.dn_id=C.dn_id"
            strSQL = strSQL + "               and B.main_dirnum='X')"
            Return myDBConn.myORATable(strSQL).Rows(0)(0)
        End Function
        Public Shared Function getDefaultLocation(ByVal custcode As String) As DataTable
            Dim strSQL As String
            strSQL = "select default_location from  IT_MVPN_DEFAULT_LOCATION where company_custcode='" & custcode & "'"
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function getDefaultLocationCount(ByVal custcode As String) As DataTable
            Dim strSQL As String
            strSQL = "select count(company_custcode) from IT_MVPN_DEFAULT_LOCATION where company_custcode='" & custcode & "'"
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function addDefaultLocation(ByVal custcode As String, ByVal def_loc As String) As Long
            Dim strSQL As String
            strSQL = " INSERT INTO IT_MVPN_DEFAULT_LOCATION VALUES ('" & custcode & "','" & def_loc & "') "
            Return myDBConn.ORAExecute(strSQL)
        End Function
        Public Shared Function updateDefaultLocation(ByVal custcode As String, ByVal def_loc As String) As Long
            Dim strSQL As String
            strSQL = " update IT_MVPN_DEFAULT_LOCATION set default_location='" & def_loc & "' where company_custcode='" & custcode & "'"
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function getCustomerCountList(ByVal custcode As String) As DataTable
            Dim strSQL As String
            
            strSQL = " select b1.custcode,a1.cnt from "
            strSQL += " ("
            strSQL += " select customer_id_high ch,count(*) cnt from customer_all a where custcode like '" & custcode & "%'"
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null and cslevel =40 "
            strSQL += " group by customer_id_high"
            strSQL += " ) a1,customer_all b1"
            strSQL += " where a1.ch=b1.customer_id(+) "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function getHolding(ByVal custcode As String) As String
            Dim strSQL As String

            strSQL = " select '10',a.custcode,'',b.ccname,'-','-','-','-','a',1 from  customer_all a,ccontact_all b"
            strSQL += " where a.custcode = '" & custcode & "' "
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null "
            strSQL += " and a.customer_id=b.customer_id(+) "
            strSQL += " and b.ccbill(+)='X' "

            Return strSQL
        End Function

        Public Shared Function getCompany(ByVal custcode As String) As String
            Dim strSQL As String

            strSQL = " select '20',a.custcode,'',b.ccname,'-','-','-','-','a',1 from  customer_all a,ccontact_all b"
            strSQL += " where a.custcode = '" & custcode & "' "
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null "
            strSQL += " and a.customer_id=b.customer_id(+) "
            strSQL += " and b.ccbill(+)='X' "

            Return strSQL
        End Function

        Public Shared Function getContractList(ByVal custcode As String, ByVal min As String, ByVal max As String) As String
            Dim strSQL As String

            strSQL = "  select * from ("
            strSQL += " select k.cslevel,k.custcode,k.ms,k.ccname,NVL(h.LOCATION,'-'),h.status,nvl(k.prvnum,'-'),nvl((select profile_desc from zone_profile where PROFILE_ID=k.prf ),'-') profil,k.cstype,rownum no "
            strSQL += " from ("
            strSQL += " select z.*,mz.INTERNAL_ID intid,nvl(mz.private_nr,'-') prvnum,mz.PROFILE prf from "
            strSQL += " ("
            strSQL += " select customer_id_high,a.cslevel,a.custcode,fn_custid_to_msisdn(a.customer_id) ms,b.ccname,a.cstype from  customer_all a,ccontact_all b"
            strSQL += " where a.custcode like '" & custcode & "%' "
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null "
            strSQL += " and a.customer_id=b.customer_id(+) "
            strSQL += " and b.ccbill(+)='X'"
            strSQL += " ) z ,msisdn_zone mz"
            strSQL += " where z.ms=mz.MSISDN(+)"
            strSQL += " )k,zone h"
            strSQL += " where k.intid=h.INTERNAL_ID(+)"
            strSQL += " and ((k.ms is not null and k.cslevel=40) or k.cslevel=20)"
            strSQL += " ) v where v.no between " & min & " and " & max

            Return strSQL
        End Function

        Public Shared Function getContractList(ByVal statement As String) As DataTable
            Return myDBConn.myORATable(statement)
        End Function


        Public Shared Function getContractAll(ByVal custcode As String) As DataTable
            Dim strSQL As String
            'strSQL = "select a.cslevel,a.custcode,fn_custid_to_msisdn(a.customer_id),b.ccname,NVL(C.LOCATION,'-'),C.STATUS,nvl(c.private_nr,'-'),nvl(c.profile_desc,'-'),a.cstype from customer_all a,ccontact_all b, "
            'strSQL = strSQL + " (SELECT A.MSISDN,B.LOCATION,B.STATUS,A.private_nr,ZP.profile_desc  from MSISDN_ZONE A, ZONE B,zone_profile ZP WHERE "
            'strSQL = strSQL + " A.INTERNAL_ID=B.INTERNAL_ID(+)  and A.profile=ZP.profile_id(+) order by 3) C "
            'strSQL = strSQL + " where a.custcode like '" & custcode & "%' "
            'strSQL = strSQL + " and a.cstype in ('a','s') and a.csdeactivated is null "
            'strSQL = strSQL + " and a.customer_id=b.customer_id(+) "
            'strSQL = strSQL + " and b.ccbill(+)='X' "
            'strSQL = strSQL + " AND fn_custid_to_msisdn(a.customer_id)=C.MSISDN(+) "
            'strSQL = strSQL + " order by 2,1 "
            strSQL = " select k.cslevel,k.custcode,k.ms,k.ccname,NVL(h.LOCATION,'-'),h.status,nvl(k.prvnum,'-'),nvl((select profile_desc from zone_profile where PROFILE_ID=k.prf ),'-'),k.cstype "
            strSQL += " from ("
            strSQL += " select z.*,mz.INTERNAL_ID intid,nvl(mz.private_nr,'-') prvnum,mz.PROFILE prf from "
            strSQL += " ("
            strSQL += " select a.cslevel,a.custcode,fn_custid_to_msisdn(a.customer_id) ms,b.ccname,a.cstype from  customer_all a,ccontact_all b"
            strSQL += " where a.custcode like '" & custcode & "%' "
            strSQL += " and a.cstype in ('a','s') and a.csdeactivated is null "
            strSQL += " and a.customer_id=b.customer_id(+) "
            strSQL += " and b.ccbill(+)='X'"
            strSQL += " ) z ,msisdn_zone mz"
            strSQL += " where z.ms=mz.MSISDN(+)"
            strSQL += " )k,zone h"
            strSQL += " where k.intid=h.INTERNAL_ID(+)"

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function getCustomerLocList(ByVal custcode As String) As DataTable
            Dim strSQL As String
            'strSQL = "select distinct location from zone where internal_id in (select internal_id from msisdn_zone where msisdn in (" + tel_str + "))"
            strSQL = " select distinct location from zone where internal_id in (select internal_id from msisdn_zone where custcode='" & custcode & "')"
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function getMsisdn_fromZone(ByVal zone As String) As DataTable
            Dim strSQL As String
            strSQL = "select msisdn from msisdn_zone where internal_id in ( "
            strSQL = strSQL + " select internal_id from zone where location='" + zone + "' and status='a') "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function addMsisdnZone(ByVal msisdn As String, ByVal custcode As String, ByVal custCompany As String) As Long
            Dim strSQL As String
            strSQL = " INSERT INTO MSISDN_ZONE VALUES ( "
            strSQL = strSQL + "ZONEINTERNAL_SEQ.nextval  , '" + msisdn + "', '" + custcode + "' , 'a', sysdate,null,null,null,'" & custCompany & "' ) "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function addZone(ByVal msisdn As String, ByVal zone As String) As Long
            Dim strSQL As String
            strSQL = " insert into zone values ( "
            strSQL = strSQL + " (select internal_id from msisdn_zone where msisdn='" + msisdn + "'), "
            strSQL = strSQL + "'" + zone + "','1',sysdate,'a',null) "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function updateZone(ByVal msisdn As String, ByVal zone As String) As Long
            Dim strSQL As String
            strSQL = "update zone set status='d' , status_update_date=sysdate where internal_id in ("
            strSQL = strSQL + "select internal_id from msisdn_zone where msisdn in (" + msisdn + ")"
            strSQL = strSQL + ") and location='" + zone + "' and status='a' "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        '***************private number add***********************
        Public Shared Function isThereMsisdn(ByVal msisdn As String) As DataTable
            Dim strSQL As String
            strSQL = " select count(*) from msisdn_zone where msisdn =" & msisdn
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function addPrivateNumber(ByVal msisdn As String, ByVal privateNum As String, ByVal custcode As String, ByVal custCompany As String) As Long
            Dim strSQL As String
            strSQL = " INSERT INTO MSISDN_ZONE VALUES ( "
            strSQL = strSQL + "ZONEINTERNAL_SEQ.nextval  , " + msisdn + ", '" + custcode + "' , 'a', sysdate," & privateNum & ",null,null,'" & custCompany & "' ) "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function updatePrivateNumber(ByVal msisdn As String, ByVal privateNum As String) As Long
            Dim strSQL As String
            'strSQL = " update MSISDN_ZONE set private_nr='" & privateNum & "' where msisdn='" & msisdn & "'"
            strSQL = " update MSISDN_ZONE set private_nr='" & privateNum & "' where internal_id in ( "
            strSQL += " select internal_id from zone where internal_id in ( "
            strSQL += " select internal_id from msisdn_zone where msisdn='" & msisdn & "') and status='a' and trunc(version_date)=trunc(sysdate) ) "

            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function deletePrivateNumber(ByVal msisdn As String) As Long
            Dim strSQL As String
            strSQL = " update MSISDN_ZONE set private_nr=null where msisdn=" & msisdn
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function isThereAnyPrivateNum(ByVal custcode As String, ByVal privateNum As String) As DataTable
            Dim strSQL As String
            strSQL = " select count(*) from msisdn_zone where custcode='" & custcode & "' and private_nr='" & privateNum & "'"
            Return myDBConn.myORATable(strSQL)
        End Function

        '***************PBX number add***********************
        Public Shared Function selectPbx(ByVal custcode As String) As DataTable
            Dim strSQL As String
            strSQL = "select msisdn from msisdn_zone where pbx='X' and company_custcode='" & custcode & "'"
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function selectPbx_zone(ByVal custcode As String) As DataTable
            Dim strSQL As String
            strSQL = " select mz.msisdn,nvl(z.location,'-'),nvl(mz.private_nr,'-') ,z.status,nvl((select profile_desc from zone_profile where profile_id=mz.profile),'-') from msisdn_zone mz,zone z "
            strSQL = strSQL + " where mz.pbx='X' and mz.company_custcode='" & custcode & "' "
            strSQL = strSQL + " and mz.internal_id=z.internal_id(+) "

            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function isTherePbx(ByVal Pbxmsisdn As String, ByVal company As String) As DataTable
            Dim strSQL As String
            strSQL = " select count(*) from msisdn_zone where msisdn ='" & Pbxmsisdn & "' and custcode=" & company
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function addPbxNumber(ByVal Pbxmsisdn As String, ByVal custcode As String, ByVal company As String) As Long
            Dim strSQL As String
            strSQL = " INSERT INTO MSISDN_ZONE VALUES ( "
            strSQL = strSQL + " ZONEINTERNAL_SEQ.nextval , '" + Pbxmsisdn + "', '" + company + "' , 'a', sysdate,null,'X',null," & custcode & " ) "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function deletePbxNumber(ByVal Pbxmsisdn As String, ByVal custcode As String) As Long
            Dim strSQL As String
            strSQL = " delete MSISDN_ZONE  where msisdn='" & Pbxmsisdn & "' and company_custcode='" & custcode & "'"
            Return myDBConn.ORAExecute(strSQL)
        End Function

        '***************Profile number add***********************

        Public Shared Function selectProfileId() As DataTable
            Dim strSQL As String
            strSQL = " select profile_id,profile_desc from zone_profile c "
            Return myDBConn.myORATable(strSQL)
        End Function

        Public Shared Function addProfileID(ByVal msisdn As String, ByVal profileId As String, ByVal custcode As String, ByVal custCompany As String) As Long
            Dim strSQL As String
            strSQL = " INSERT INTO MSISDN_ZONE VALUES ( "
            strSQL = strSQL + "ZONEINTERNAL_SEQ.nextval  , '" + msisdn + "', '" + custcode + "' , 'a', sysdate,null,null,'" & profileId & "','" & custCompany & "' ) "
            Return myDBConn.ORAExecute(strSQL)
        End Function

        Public Shared Function updateProfileID(ByVal msisdn As String, ByVal profileId As String) As Long
            Dim strSQL As String
            strSQL = " update MSISDN_ZONE set profile=" & profileId & " where msisdn=" & msisdn
            Return myDBConn.ORAExecute(strSQL)
        End Function


    End Class
End Namespace
